<?php
class Admin_Model_Clients extends My_Model_Db
{
    protected $_name = 'clients';
	protected $_primary  = 'p_clients';
	protected $_mainTable = 'clients';
    protected $_idFieldName = 'p_clients';

	public function getListClient($num_vente, $org='all', $ordre='nom', $tri='asc')
	{
        $sql = "
		SELECT
		clients.p_clients,
		clients.nom,
		clients.prenom,
		clients.email,
		clients.dtvalid,
		clients.dtdemandevalid,
		clients.dtconfirm,
		clients_ventes.num_client
		FROM
		clients
		Left Join clients_ventes ON clients.p_clients = clients_ventes.f_clients
		where deleted = 0 and p_clients > 0";
		if(isset($org) && $org == "arc") {
			$sql .= " and archive = 1 ";
		} else {
			$sql .= " and archive = 0 ";
		}
		$sql .= " and f_ventes = $num_vente  ";
		$sql .= " order by $ordre $tri";
   //echo $sql;exit;
		return $this->getAdapter()->fetchAll($sql);
	}

    public function getListClientArchived($num_vente, $org='all', $ordre='nom', $tri='asc')
    {
        $sql = "
            SELECT
            clients.p_clients,
            clients.nom,
            clients.prenom,
            clients.email,
            clients.dtvalid,
            clients.dtdemandevalid,
            clients.dtconfirm,
            clients_ventes.num_client
            FROM
            clients
            Left Join clients_ventes ON clients.p_clients = clients_ventes.f_clients
            where deleted = 0 and p_clients > 0
            and f_ventes = $num_vente
            and dtcreat > '2009-03-24'
            and dtconfirm <> ''
            order by dtcreat DESC, $ordre $tri";

            return $this->getAdapter()->fetchAll($sql);

    }

	public function updateClientsVentes($numVente)
	{
        $select = $this->getAdapter()->select();
        $select->from('clients', array('p_clients'));
        $select->join('clients_ventes', 'clients_ventes.f_clients = clients.p_clients', array());
        $select->where('f_ventes = ?', $numVente);
        $select->where('p_clients > ?', 0);
        $select->where('deleted = ?', 0);
        $select->where('archive = ?', 0);
        $result = $this->getAdapter()->fetchAll($select);
        if ($result) {
            $rows = array();
            foreach ($result as $row) {
               $rows[] = $row['p_clients'];
            }
            $where = $this->getAdapter()->quoteInto('f_ventes = ?', $numVente);
            $where .= $this->getAdapter()->quoteInto(' AND f_clients IN (?)', $rows);
            $this->getAdapter()->update('clients_ventes', array('num_client' => 'n'), $where);
            return true;
        }
	}

    public function removeClient($num, $num_vente)
    {
        $qM = "SELECT email,userid FROM clients WHERE p_clients = $num";
        $rowM = $this->getAdapter()->fetchRow($qM);
        $email = $rowM['email'] . "DEL";
        $userid = $rowM['userid'] . date('Y-m-d');

        $q  = "update clients set ";
        $q .= "dtdeleted='". date('Y-m-d') ."'";
        $q .= ",hrdeleted='". date('His') ."'";
        $q .= ",deleted=1, email='". $email ."'";
        $q .= ",userid='". $userid ."'";
        $q .= " where p_clients = $num";

        $this->getAdapter()->query($q);

        $sql = "DELETE FROM clients_ventes WHERE f_clients = $num and f_ventes in (select p_ventes from ventes where dtvente >= (select dtvente from ventes where p_ventes = $num_vente))";
		$this->getAdapter()->query($sql);

        return true;
    }

    public function validateEmail($email)
    {
        $select = $this->getAdapter()->select()
        ->from($this->_mainTable, array($this->_idFieldName))
        ->where('email = ?', $email);

        return $this->getAdapter()->fetchOne($select);
    }

    public function validateUserid($userId)
    {
        $select = $this->getAdapter()->select()
        ->from($this->_mainTable, array($this->_idFieldName))
        ->where('userid = ?', $userId);

        return $this->getAdapter()->fetchOne($select);
    }

    public function updateNumClient($id, $numVente)
    {
        $sql = "UPDATE clients_ventes SET num_client = 'n' WHERE f_clients = $id AND f_ventes IN (SELECT p_ventes FROM ventes WHERE dtvente >= (SELECT dtvente FROM ventes WHERE p_ventes = $numVente))";
        $this->getAdapter()->query($sql);
    }

	public function getCLientsByEmail($email) {
	   $sql = "SELECT p_clients FROM clients WHERE email = '". addslashes($email) ."'";
       return $this->getAdapter()->fetchRow($sql);
	}

	public function getCLientsByUser($user) {
	   $sql = "SSELECT p_clients FROM clients WHERE userid='$userid'";
       return $this->getAdapter()->fetchRow($sql);
	}

	public function getListCity() {
		$sql = "SELECT nom,p_civilites FROM civilites WHERE deleted = 0 order by nom";
       return $this->getAdapter()->fetchAll($sql);
	}

	public function getListPay() {
		$sql = "SELECT p_pays,nom FROM pays WHERE deleted = 0 order by nom";
       return $this->getAdapter()->fetchAll($sql);
	}

	public function isClient($num_client, $numVente)
	{
		$sql = "SELECT clients.p_clients FROM clients
			Left Join clients_ventes ON clients.p_clients = clients_ventes.f_clients
			where deleted = 0
			and clients_ventes.f_ventes = $numVente
			and clients_ventes.num_client =  '" . $num_client . "'";

		return $this->getAdapter()->query($sql)->rowCount();
	}

	public function getMaxClient()
	{
		$sql = "select max(p_clients) as lemax from clients";
		$row_cle = $this->getAdapter()->fetchRow($sql);
		return $row_cle['lemax'] + 1;
	}

	public function getVentes($numVente)
	{
		$sql = "SELECT p_ventes FROM ventes WHERE dtvente >= (SELECT dtvente FROM ventes WHERE p_ventes = $numVente)";
		return $this->getAdapter()->fetchAll($sql);
	}

	public function insetClientEvent($cle, $p_ventes, $numclient, $com)
	{
		$sql = "INSERT INTO clients_ventes (f_clients,f_ventes,num_client,commission) VALUES ($cle,$p_ventes, '$numclient',$com)";
		$this->getAdapter()->query($sql);
	}

	public function getClientById($num) {
		$sql = "SELECT * FROM clients WHERE p_clients = $num";
		return $this->getAdapter()->fetchRow($sql);
	}

	public function getClientVentes($num, $numVente) {
		$sql = "SELECT num_client,commission FROM clients_ventes WHERE f_clients = $num and f_ventes = $numVente";
		return $this->getAdapter()->fetchRow($sql);
	}

	public function updateClientVentes($num, $numVente)
	{
		$sql = "UPDATE clients_ventes SET num_client = 'n' where f_clients = $num and f_ventes in (select p_ventes from ventes where dtvente >= (select dtvente from ventes where p_ventes = $numVente))";
		$this->getAdapter()->query($sql);
	}

	public function fecthRowClient($sql)
	{
		return $this->getAdapter()->fetchRow($sql);
	}

	public function rowCountClient($sql)
	{
		return $this->getAdapter()->query($sql)->rowCount();
	}

	public function updateRowClient($sql)
	{
		return $this->getAdapter()->query($sql);
	}

	public function getClientForPdf($id = null, $numVente = 0)
	{
        $fieldSelect = array(
            'vendeur',
	        'acheteur',
	        'num_identite',
	        'email',
	        'prenom',
	        'nom',
	        'adresse',
	        'cp',
	        'ville',
	        'tel1',
	        'tel2',
	        'tel3',
	        'tel4',
	        'compte_banque',
	        'tva',
	        'co',
	        'adresse2'
        );
        $select = $this->getAdapter()->select()
        ->from($this->_name, $fieldSelect)
        ->joinLeft('pays', 'clients.f_pays = pays.p_pays', array('pays' => 'nom'))
        ->joinLeft('civilites', 'clients.f_civilites = civilites.p_civilites',
            array('civilite' => 'nom', 'lenom' => 'CONCAT(`civilites`.`nom`, \' \', `clients`.`prenom`, \' \', `clients`.`nom`)')
        )
        ->join('clients_ventes', 'clients_ventes.f_clients = clients.p_clients', array('commission', 'num_client'))
        ->where('`clients`.`p_clients` = ?', $id)
        ->where('`clients_ventes`.`f_ventes` = ?', $numVente);

        return $this->getAdapter()->fetchRow($select);
	}

	public function save($data, $id = null)
	{
	    if ($id) {
	        $where = $this->getAdapter()->quoteInto($this->_idFieldName . ' = ?', $id);
	        $this->getAdapter()->update($this->_mainTable, $data, $where);
	    } else {
	        $select = $this->getAdapter()->select()
	        ->from($this->_mainTable, array('max_id' => 'MAX(`' . $this->_idFieldName . '`)'));
	        $maxId = $this->getAdapter()->fetchOne($select);
	        $insertId = $maxId + 1;
	        $data[$this->_idFieldName] = $insertId;
	        $this->getAdapter()->insert($this->_mainTable, $data);
	    }
	    return true;
	}
}